package com.guojian.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import javax.annotation.Nonnull;
import javax.annotation.Nullable;
import javax.sql.DataSource;

import org.springframework.beans.factory.InitializingBean;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.util.Assert;

import com.alibaba.fastjson.JSON;
import com.google.common.collect.Lists;
import com.guojian.common.utils.StringUtilsx;
import com.guojian.common.view.PageModel;
import com.guojian.common.view.PageView;
import com.guojian.jdbc.filter.SqlFilter;
import com.guojian.jdbc.mapper.RowMapperTemplate;
import com.guojian.jdbc.mapper.SingleColumnRowMapperOfPage;
import com.guojian.jdbc.sql.EntityInfo;
import com.guojian.jdbc.sql.SqlContext;
import com.guojian.jdbc.sql.SqlParser;
import com.guojian.jdbc.utils.DataAccessUtilsX;

import lombok.extern.slf4j.Slf4j;

/**
 * GuoJian 
 * 
 * JDBC数据操作核心类,增加自动分页,和数据转换
 * 
 * JdbcTemplateX的运行需要  @DataSource 和  @SqlParser
 * 
 */

@Slf4j
public class JdbcTemplateX implements InitializingBean {

	/**
	 * 过滤器列表
	 */
	private List<SqlFilter> sqlFilterList;

	/**
	 * sql解析器
	 */
	private SqlParser sqlParser;

	/**
	 * jdbc模板类
	 */
	private JdbcTemplate jdbcTemplate;

	/**
	 * 数据源
	 */
	private DataSource dataSource;

	public JdbcTemplateX(@Nonnull DataSource dataSource) {
		setJdbcTemplate(dataSource);
		afterPropertiesSet();
	}

	public JdbcTemplateX(@Nonnull JdbcTemplate jdbcTemplate) {
		setJdbcTemplate(jdbcTemplate);
		afterPropertiesSet();
	}

	public JdbcTemplateX(@Nonnull DataSource dataSource, @Nonnull SqlParser sqlParser) {
		setJdbcTemplate(dataSource);
		this.sqlParser = sqlParser;
	}

	public JdbcTemplateX(@Nonnull JdbcTemplate jdbcTemplate, @Nonnull SqlParser sqlParser) {
		setJdbcTemplate(jdbcTemplate);
		this.sqlParser = sqlParser;
	}

	public JdbcTemplateX(@Nonnull DataSource dataSource, DBType dbType) {
		setJdbcTemplate(dataSource);
		this.sqlParser = SqlContext.getSqlParser(dbType);
	}

	/**
	 * 对sql进行过滤
	 * @param sql
	 * @return
	 */
	public String filterSql(String sql) {
		if (getSqlFilterList() == null)
			return sql;

		String newSql = sql;
		for (SqlFilter filter : getSqlFilterList()) {
			newSql = filter.filter(newSql);
		}
		return newSql;
	}

	public <T> T insertWithEntity(T object) {
		List<Object> sqlParams = Lists.newArrayList();
		EntityInfo<T> entityInfo = new EntityInfo<T>(object);
		String insertSql = entityInfo.buildInsertSql(sqlParams);
		log.debug("insertEntity sql: [{}], params: [{}]", insertSql, StringUtilsx.collectionToCommaDelimitedString(sqlParams));
		Map<String, Object> returnGeneratedMap = this.insertWithReturnSingleKey(insertSql, entityInfo.getGeneratedColumnNameFieldMap().keySet().toArray(new String[0]), sqlParams.toArray());
		log.debug("insertEntity gneratedColumnNameValues: {}", JSON.toJSONString(returnGeneratedMap));
		if (returnGeneratedMap == null)
			return object;
		try {
			for (String returnGeneratedKey : returnGeneratedMap.keySet()) {
				entityInfo.getGeneratedColumnNameFieldMap().get(returnGeneratedKey).set(object, returnGeneratedMap.get(returnGeneratedKey));
			}
		} catch (IllegalArgumentException | IllegalAccessException e) {
			throw new RuntimeException(e);
		}
		return object;
	}

	/**
	 * ID注解的属性值作为where条件
	 * @param object
	 * @return
	 */
	public <T> int deleteByEntity(T object) {
		List<Object> sqlParams = Lists.newArrayList();
		EntityInfo<T> entityInfo = new EntityInfo<T>(object);
		String deleteSql = entityInfo.buildDeleteSql(sqlParams);
		int effectRows = getJdbcTemplate().update(deleteSql, sqlParams.toArray());
		log.debug("DeleteEntity effectRows: [{}], sql: [{}], params: [{}]", effectRows, deleteSql, StringUtilsx.collectionToCommaDelimitedString(sqlParams));
		return effectRows;
	}

	/**
	 * 不为空的属性值作为where条件
	 * @param object
	 * @return
	 */
	public <T> T selectObjectByEntity(T object, Class<T> c) {
		return DataAccessUtilsX.getNullableSingleResult(selectListByEntity(object, c));
	}

	public <T> List<T> selectListByEntity(T object, Class<T> c) {
		List<Object> sqlParams = Lists.newArrayList();
		EntityInfo<T> entityInfo = new EntityInfo<T>(object);
		String selectSql = entityInfo.buildSelectSql(sqlParams);
		log.debug("SelectSingleByEntity sql: [{}], params: [{}]", selectSql, StringUtilsx.collectionToCommaDelimitedString(sqlParams));
		return selectBeanList(selectSql, c, null, sqlParams.toArray());
	}

	/**
	 * ID注解的属性值作为where条件
	 * @param object
	 * @return
	 */
	public <T> int updateByEntity(T object) {
		List<Object> sqlParams = Lists.newArrayList();
		EntityInfo<T> entityInfo = new EntityInfo<T>(object);
		String updateSql = entityInfo.buildUpdateSql(sqlParams);
		int effectRows = getJdbcTemplate().update(updateSql, sqlParams);
		log.debug("UpdateByEntity effectRows: [{}], sql: [{}], params: [{}]", effectRows, updateSql, StringUtilsx.collectionToCommaDelimitedString(sqlParams));
		return effectRows;
	}

	/**
	 * 分页查询(根据数据库的类型,自动生成count语句和分页语句)
	 */

	public PageView<Map<String, Object>> selectPageListMapObject(String sql, PageModel page, Object... params) {
		return selectPageListMapObject(sql, page, null, false, params);
	}

	public PageView<Map<String, Object>> selectPageListMapObject(String sql, PageModel page, JdbcDataFormat<Map<String, Object>> format, Object... params) {
		return selectPageListMapObject(sql, page, format, false, params);
	}

	public PageView<Map<String, Object>> selectPageListMapObject(String sql, PageModel page, JdbcDataFormat<Map<String, Object>> format, boolean isAddLowerCaseOfName, Object... params) {
		RowMapper<Map<String, Object>> rowMapper = RowMapperTemplate.getMapRowMapper(Object.class, isAddLowerCaseOfName, format);
		return selectPage(sql, page, rowMapper, params);
	}

	public <T> PageView<T> selectOneColumnPageList(String sql, PageModel page, Class<T> cType, Object... params) {
		return selectPage(sql, page, new SingleColumnRowMapperOfPage<>(cType), params);
	}

	public <T> PageView<T> selectPageListBean(String sql, PageModel page, Class<T> cType, Object... params) {
		return selectPageListBean(sql, page, cType, params);
	}

	public <T> PageView<T> selectPageListBean(String sql, PageModel page, Class<T> cType, JdbcDataFormat<T> format, Object... params) {
		RowMapper<T> rowMapper = RowMapperTemplate.builder(cType).createBeanPropertyRowMapper(format);
		return selectPage(sql, page, rowMapper, params);
	}

	/**
	 * 分页查询
	 * @param sql
	 * @param page
	 * @param rowMapper
	 * @param params
	 * @return
	 */
	public <T> PageView<T> selectPage(String sql, PageModel page, RowMapper<T> rowMapper, Object... params) {
		String newSql = filterSql(sql);
		int total = selectObject(getSqlParser().getCountSql(newSql), Integer.class, params);
		String pageSql = getSqlParser().getPageSql(newSql, page.getPageUIStartNum() - 1, page.getRows());
		List<T> rows = select(pageSql, rowMapper, params);
		return new PageView<T>(rows, total);
	}

	public <T> T selectObject(String sql, Class<T> requiredType, Object... params) {
		return DataAccessUtilsX.getNullableSingleResult(selectOneColumnList(sql, requiredType, params));
	}

	public <T> List<T> selectOneColumnList(String sql, Class<T> requiredType, Object... params) {
		return select(sql, new SingleColumnRowMapperOfPage<>(requiredType), params);
	}

	/**
	 * 移动端分页(不需要计算total,只需要nextPageNum)
	 */
	public List<Map<String, Object>> queryMobileListByPage(String sql, PageModel page, String[] rparams, boolean isAddLowerCaseOfName, JdbcDataFormat<Map<String, Object>> format, Object... params) {
		return selectListMapObject(convertToPageSql(sql, page, rparams), isAddLowerCaseOfName, format, params);
	}

	private String convertToPageSql(String sql, PageModel page, String[] rparams) {
		String rsql = getSqlParser().getPageSql(sql, page.getPageUIStartNum() - 1, page.getRows());
		if (rparams == null || rparams.length == 0) {
			return rsql;
		}
		for (int i = 1; i <= rparams.length; i++) {
			rsql = rsql.replace("##" + i, rparams[i - 1]);
		}
		return rsql;
	}

	public Map<String, Object> selectMapObject(String sql, boolean isAddLowerCaseOfName, JdbcDataFormat<Map<String, Object>> format, Object... args) {
		return DataAccessUtilsX.getNullableSingleResult(selectListMapObject(sql, isAddLowerCaseOfName, format, args));
	}

	public List<Map<String, Object>> selectListMapObject(String sql, boolean isAddLowerCaseOfName, JdbcDataFormat<Map<String, Object>> format, Object... args) throws DataAccessException {
		RowMapper<Map<String, Object>> rowMapper = RowMapperTemplate.getMapRowMapper(Object.class, isAddLowerCaseOfName, format);
		return select(sql, rowMapper, args);
	}

	public <T> List<T> select(String sql, RowMapper<T> rowMapper, Object... args) {
		return getJdbcTemplate().query(filterSql(sql), rowMapper, args);
	}

	public Map<String, String> selectMapString(String sql, boolean isAddLowerCaseOfName, JdbcDataFormat<Map<String, String>> format, Object... args) {
		return DataAccessUtilsX.getNullableSingleResult(selectListMapString(sql, isAddLowerCaseOfName, format, args));
	}

	public List<Map<String, String>> selectListMapString(String sql, boolean isAddLowerCaseOfName, JdbcDataFormat<Map<String, String>> format, Object... args) {
		RowMapper<Map<String, String>> rowMapper = RowMapperTemplate.getMapRowMapper(String.class, isAddLowerCaseOfName, format);
		return select(sql, rowMapper, args);
	}

	public <T> List<T> selectBeanList(String sql, Class<T> tClass, JdbcDataFormat<T> format, Object... args) {
		RowMapper<T> rowMapper = RowMapperTemplate.getBeanRowMapper(tClass, format);
		return select(sql, rowMapper, args);
	}

	public <T> T selectBean(String sql, Class<T> tClass, JdbcDataFormat<T> format, Object... args) {
		return DataAccessUtilsX.getNullableSingleResult(selectBeanList(sql, tClass, format, args));
	}

	@Deprecated
	public int insertWithReturnKey1(String sql, @Nullable String[] keyColumnNames, Object... params) {
		KeyHolder keyHolder = new GeneratedKeyHolder();
		int rows = getJdbcTemplate().update(new PreparedStatementCreator() {
			@Override
			public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
				//PreparedStatement ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);Oracle中返回的是rowid
				PreparedStatement ps = con.prepareStatement(sql, keyColumnNames);
				for (int j = 0; j < params.length; j++) {
					ps.setObject(j + 1, params[j]);
				}
				return ps;
			}
		}, keyHolder);

		if (rows > 0) {
			return keyHolder.getKey().intValue();
		}
		return 0;
	}

	/**
	 * 插入数据并返回指定列的值使用generatedKeysColumnNames指定
	 * @param sql
	 * @param generatedKeysColumnNames
	 * @param params
	 * @return
	 */
	public KeyHolder insertWithReturnKeyHolder(String sql, @Nullable String[] generatedKeysColumnNames, Object... params) {
		KeyHolder keyHolder = new GeneratedKeyHolder();
		PreparedStatementCreatorFactory pscf = new PreparedStatementCreatorFactory();
		if (generatedKeysColumnNames != null && generatedKeysColumnNames.length > 0) {
			pscf.setGeneratedKeysColumnNames(generatedKeysColumnNames);
		} else {
			pscf.setReturnGeneratedKeys(false);
		}
		PreparedStatementCreator psc = pscf.getPreparedStatementCreator(sql, params);
		if (!pscf.isReturnGeneratedKeys()) {
			getJdbcTemplate().update(psc);
		} else {
			getJdbcTemplate().update(psc, keyHolder);
		}
		return keyHolder;
	}

	public List<Map<String, Object>> insertWithReturnListKey(String sql, @Nullable String[] generatedKeysColumnNames, Object... params) {
		return insertWithReturnKeyHolder(sql, generatedKeysColumnNames, params).getKeyList();
	}

	public <T> T insertWithReturnClassKey(String sql, @Nullable String[] generatedKeysColumnNames, Class<T> ctype, Object... params) {
		return insertWithReturnKeyHolder(sql, generatedKeysColumnNames, params).getKeyAs(ctype);
	}

	public Number insertWithReturnNumberKey(String sql, @Nullable String[] generatedKeysColumnNames, Object... params) {
		return insertWithReturnKeyHolder(sql, generatedKeysColumnNames, params).getKey();
	}

	public Map<String, Object> insertWithReturnSingleKey(String sql, @Nullable String[] generatedKeysColumnNames, Object... params) {
		return DataAccessUtilsX.getNullableSingleResult(insertWithReturnListKey(sql, generatedKeysColumnNames, params));
	}

	/**
	 * 所有操作成功则返回1
	 *
	 * @param sql
	 * @param batchArgs
	 * @return
	 */
	public int batchUpdateWithAllValid(String sql, List<Object[]> batchArgs) {
		int[] effectRows = getJdbcTemplate().batchUpdate(sql, batchArgs);
		for (int effectRow : effectRows) {
			if (effectRow == 0) {
				return effectRow;
			}
		}
		return 1;
	}

	public int update(String sql, Object... args) {
		return getJdbcTemplate().update(sql, args);
	}

	@Override
	public void afterPropertiesSet() {
		initSqlParser();
	}

	/**
	 * 初始化Sql解析器
	 */
	protected void initSqlParser() {
		if (getSqlParser() == null) {
			log.debug("SqlParser is not set, next will init sqlParser by current DataSource: {}", getDataSource());
			String driverUrl = getDriverUrl(getDataSource());
			Assert.hasText(driverUrl, "driverUrl must not be empty");
			log.debug("Find driverUrl: [{}]", driverUrl);

			if (driverUrl.contains(DBType.oracle.name())) {
				this.sqlParser = SqlContext.getSqlParser(DBType.oracle);
			} else if (driverUrl.contains(DBType.sqlserver.name())) {
				this.sqlParser = SqlContext.getSqlParser(DBType.sqlserver);
			} else {
				//不是oracle和sqlserver则统一当做mysql处理
				this.sqlParser = SqlContext.getSqlParser(DBType.mysql);
			}
		}
		log.debug("SqlParser 初始化完成 : {}, DBType: {}", sqlParser.toString(), sqlParser.getDBType());
	}

	private String getDriverUrl(DataSource dataSource) {
		Connection conn = null;
		try {
			conn = dataSource.getConnection();
			Assert.notNull(conn, "GetDriverUrl error, Connection is null");
			String driverUrl = conn.getMetaData().getURL().toLowerCase();
			log.debug("Find Current Connection: [{}]", conn);
			return driverUrl;
		} catch (SQLException e) {
			log.debug("GetDriverUrl error: ", e);
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
			}
		}
		return null;
	}

	public SqlParser getSqlParser() {
		return sqlParser;
	}

	public List<SqlFilter> getSqlFilterList() {
		return sqlFilterList;
	}

	public void setSqlFilterList(List<SqlFilter> sqlFilterList) {
		this.sqlFilterList = sqlFilterList;
	}

	public void setSqlParser(SqlParser sqlParser) {
		this.sqlParser = sqlParser;
	}

	public DataSource getDataSource() {
		return dataSource;
	}

	public void setDataSource(DataSource dataSource) {
		Assert.notNull(dataSource, "DataSource must not be null");
		this.dataSource = dataSource;
	}

	private void setJdbcTemplate(DataSource dataSource) {
		setDataSource(dataSource);
		this.jdbcTemplate = new JdbcTemplate(dataSource);
		Assert.notNull(jdbcTemplate, "JdbcTemplate must not be null");
	}

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		Assert.notNull(jdbcTemplate, "JdbcTemplate must not be null");
		this.jdbcTemplate = jdbcTemplate;
		setDataSource(this.jdbcTemplate.getDataSource());
	}

	public JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}

	public static void main(String[] args) {

	}

}
